import sqlite3
import globals.var
class Database:
    def __init__(self, dbname:str = None):
        if not dbname:
            dbname = globals.var.dbname
        self.conn = sqlite3.connect(dbname)

    def get_conn(self):
        return self.conn

    def close_conn(self):
        self.conn.commit()
        self.conn.close()

    def create_table(self, table_name:str, fields_list:list[dict],foreign_list:list[dict] = None):
        '''
        fields_list = [
            {'name': 'id', 'type': 'INTEGER PRIMARY KEY'},
        ]
        foreign_list = [
            {'key': 'device_id', 'ref':'devices (id)'},
        ]
        '''
        sql_create_table = f"CREATE TABLE IF NOT EXISTS {table_name}("
        for field in fields_list:
            sql_create_table += f"{field['name']} {field['type']},"
        if foreign_list:
            for foreign in foreign_list:
                sql_create_table += f"FOREIGN KEY ({foreign['key']}) REFERENCES {foreign['ref']}"
        sql_create_table = sql_create_table.rstrip(',') + ")"
        self.conn.execute(sql_create_table)

    def insert(self, table_name:str, fields:list, values:tuple):
        sql_insert = f"INSERT INTO {table_name} ({','.join(fields)}) VALUES ({','.join(['?']*len(values))})"
        self.conn.execute(sql_insert, values)

    def delete(self, table_name:str, condition:str):
        sql_delete = f"DELETE FROM {table_name} WHERE {condition}"
        self.conn.execute(sql_delete)
    
    def update(self, table_name:str, fields:list, values:tuple, condition:str):
        '''
        table_name:devices \n
        fields:['name','ip','port']\n
        values:('设备名','10.1.1.','22')\n
        condition:id=1
        '''
        expression = '=?,'.join(fields) + '=?'
        sql_update = f"UPDATE {table_name} SET {expression} WHERE {condition}"
        self.conn.execute(sql_update,values)
    # def update(self, table_name:str, set_expression:str, condition:str):
    #     '''
    #     table_name:devices set 
    #     set_expression:"name='name',port='22'"
    #     condition:id=1
    #     '''
    #     sql_update = f"UPDATE {table_name} SET {set_expression} WHERE {condition}"
    #     self.conn.execute(sql_update)

    def select(self, table_name:str, fields:list[str], condition:str=None, limit:str=None,params:tuple=None) -> list[tuple]:
        sql_select = f"SELECT {','.join(fields)} FROM {table_name}"
        if condition:
            sql_select += f" WHERE {condition}"
        if limit:
            sql_select += f" LIMIT {limit}"
        if params:
            res = self.conn.execute(sql_select,params).fetchall()
        else:
            res = self.conn.execute(sql_select).fetchall()
        return res